﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using util;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
using dto;
using common;
using System.Text.RegularExpressions;
using System.Net;
using System.Configuration;
using System.Collections;
namespace dao
{
    public class UsersDAO
    {
        public string getIP()
        {
            string strHostName = "";
            strHostName = Dns.GetHostName();

            IPHostEntry ipEntry = Dns.GetHostEntry(strHostName);

            IPAddress[] addr = ipEntry.AddressList;

            return addr[2].ToString();

        }
        /* Author : Jenny
         * Date : 29/3/2012
         * function loginByUserNameAndPassword
         * parameter : username, password
         * check username and password when user login
         * return value : Boolean
         */
        public int loginByUserNameAndPassword(String username, String password)
        {
            int result = 1;
            if (Constant.IP == null)
                Constant.IP = getIP();
            UserLoginDTO userLogin = new UserLoginDTO();
            try
            {
                DataUtil.connect();
                MySqlParameter[] arrPam = new MySqlParameter[1];
                arrPam[0] = new MySqlParameter("_UserName", username);
                MySqlDataReader reader = DataUtil.executeReader("sp_checkUserNameExists", arrPam);

                if (reader.Read())
                {
                    if (reader["Username"].ToString().Equals(username))
                    {
                        if (reader["Status"].ToString().Equals("0"))
                        {
                            result = -3;
                        }
                        else if (reader["Status"].ToString().Equals("2"))
                        {
                            result = -4;
                        }
                        else if (reader["Password"].ToString().Equals(password))
                        {
                            updateCountLoginAndGetUser(reader["ID"].ToString(), Constant.DR_ACTIONS_VARIABLE["varLogin"].ToString(), "successful");
                            result = 0;
                        }
                        else
                        {
                            result = -1;
                            Constant.COUNT_LOGIN_FAIL = Convert.ToInt32(reader["CountLoginFail"].ToString()) + 1;
                            updateCountLoginFail(reader["ID"].ToString(), Constant.DR_ACTIONS_VARIABLE["varLogin"].ToString(), "fail");
                        }
                    }
                }
                else
                {
                    result = -2;
                }
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }
            DataUtil.disconnect();
            return result;
        }
        /* Author : Jenny
         * Date : 29/3/2012
         * function updateCountLogin
         * parameter id : login id
         * parameter action : such as add, update, delete write to log
         * parameter content : content of this action
         * update how many times user login and the last login times and get user details assign to 2 user DTO
         * return value : Boolean
         */
        public Boolean updateCountLoginAndGetUser(String id, String action, String content)
        {
            try
            {
                DataUtil.connect();
                MySqlParameter[] arrpam = new MySqlParameter[4];
                arrpam[0] = new MySqlParameter("_IDLogin", id);
                arrpam[1] = new MySqlParameter("_IP", Constant.IP);
                arrpam[2] = new MySqlParameter("_Actions", action);
                arrpam[3] = new MySqlParameter("_Contents", content);
                
                String cmdText = "sp_updateCountLogin";
                MySqlDataReader reader = DataUtil.executeReader(cmdText, arrpam);
                if (reader.Read())
                {
                    UserLoginDTO userLogin = new UserLoginDTO();
                    userLogin.Id = id;
                    userLogin.Username = reader["UserName"].ToString();
                    userLogin.Password = reader["Password"].ToString();
                    Constant.LOG_ID = Convert.ToInt32(reader["LogID"].ToString());

                    UserInformationsDTO userInformation = new UserInformationsDTO();
                    userInformation.Id = reader["UserInformationsID"].ToString();
                    userInformation.FirstName = reader["FirstName"].ToString();
                    userInformation.LastName = reader["LastName"].ToString();
                    userInformation.StaffCode = reader["StaffCode"].ToString();
                    userInformation.Address = reader["Address"].ToString();
                    userInformation.Email = reader["Email"].ToString();
                    userInformation.Phone1 = reader["Phone1"].ToString();
                    userInformation.Phone2 = reader["Phone2"].ToString();
                    userInformation.Images = reader["Images"].ToString();
                    userInformation.Birth = reader["Birth"].ToString();
                    userInformation.Fax = reader["Fax"].ToString();
                    userInformation.IdNumber = reader["IdNumber"].ToString();

                    Constant.USER_LOGIN = userLogin;
                    Constant.USER_INFORMATIONDTO = userInformation;
                }
                DataUtil.disconnect();
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                return false;
            }
            return true;
        }
        /* Author : Jenny
        * Date : 29/3/2012
        * function updateCountLoginFail
        * parameter id : login id
        * parameter action : such as add, update, delete write to log
        * parameter content : content of this action
        * update how many times user login fail, if fail 3 times account will be locked
        * return value : Boolean
        */
        public Boolean updateCountLoginFail(String id, String action, String content)
        {
            Boolean result = true;
            try
            {
                DataUtil.connect();
                MySqlParameter[] arrpam = new MySqlParameter[6];
                arrpam[0] = new MySqlParameter("_ID", id);
                arrpam[1] = new MySqlParameter("_IP", Constant.IP);
                arrpam[2] = new MySqlParameter("_CountLoginFail", Constant.COUNT_LOGIN_FAIL);
                arrpam[3] = new MySqlParameter("_Actions", action);
                arrpam[4] = new MySqlParameter("_Contents", content);
                arrpam[5] = new MySqlParameter("_ActionsLock", Constant.DR_ACTIONS_VARIABLE["varSystemLockUser"].ToString());
                String cmdText = "sp_updateCountLoginFail";
                if (DataUtil.executeNonQuery(cmdText, arrpam))
                    result = true;
                else
                    result = false;
                
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                result = false;
            }
            DataUtil.disconnect();
            return result;
        }
        /* Author : Jenny
         * Date : 30/03/2012
         * function insertUser
         * paramter userInformation : information of user
         * parameter userLogin : details of user about login
         * parameter action : such as add, update, delete write to log
         * parameter content : content of this action
         * insert a record in table userinformations
         * return : Boolean
         */
        public Boolean insertUser(UserInformationsDTO userInformations, UserLoginDTO userLogin, String action, String content)
        {
            Boolean result = true;
            try
            {
                DataUtil.connect();
                String cmd = "sp_insertUser";
                MySqlParameter[] arrParams = new MySqlParameter[11];
                arrParams[0] = new MySqlParameter("_IDLogin", MySqlDbType.Int32);
                arrParams[0].Direction = ParameterDirection.Output;
                arrParams[1] = new MySqlParameter("_FirstName", userInformations.FirstName);
                arrParams[2] = new MySqlParameter("_LastName", userInformations.LastName);
                arrParams[3] = new MySqlParameter("_Email", userInformations.Email);
                arrParams[4] = new MySqlParameter("_Phone1", userInformations.Phone1);
                arrParams[5] = new MySqlParameter("_UserName", userLogin.Username);
                arrParams[6] = new MySqlParameter("_Password", userLogin.Password);
                arrParams[7] = new MySqlParameter("_Actions", action);
                arrParams[8] = new MySqlParameter("_Contents", content);
                arrParams[9] = new MySqlParameter("_IDLog", Constant.LOG_ID);
                arrParams[10] = new MySqlParameter("_CompanyID", userInformations.CompanyID);
                result = DataUtil.executeNonQuery(cmd, arrParams);
                if (result)
                {
                    userInformations.Id = arrParams[0].Value.ToString();
                }
            }
            catch (Exception ex)
            {
                result = false;
            }
            DataUtil.disconnect();
            return result;
        }
        /* Author : Jenny
         * Date : 30/03/2012
         * function updateUserInformations
         * paramter userInformation : information of user
         * parameter action : such as add, update, delete write to log
         * parameter content : content of this action
         * insert a record in table userinformations
         * return : Boolean
         */
        public Boolean updateUserInformations(UserInformationsDTO userInformations, String action, String content)
        {
            Boolean result = true;
            try
            {
                DataUtil.connect();
                String cmd = "sp_updateUserInformations";
                MySqlParameter[] arrParams = new MySqlParameter[14];
                arrParams[0] = new MySqlParameter("_ID", userInformations.Id);
                arrParams[1] = new MySqlParameter("_FirstName", userInformations.FirstName);
                arrParams[2] = new MySqlParameter("_LastName", userInformations.LastName);
                arrParams[3] = new MySqlParameter("_Email", userInformations.Email);
                arrParams[4] = new MySqlParameter("_Address", userInformations.Address);
                arrParams[5] = new MySqlParameter("_Phone1", userInformations.Phone1);
                arrParams[6] = new MySqlParameter("_Phone2", userInformations.Phone2);
                arrParams[7] = new MySqlParameter("_Actions", action);
                arrParams[8] = new MySqlParameter("_Contents", content);
                arrParams[9] = new MySqlParameter("_IDLog", Constant.LOG_ID);
                arrParams[10] = new MySqlParameter("_Images", userInformations.Images);
                arrParams[11] = new MySqlParameter("_Birth", userInformations.Birth);
                arrParams[12] = new MySqlParameter("_IDNumber", userInformations.IdNumber);
                arrParams[13] = new MySqlParameter("_Fax", userInformations.Fax);
                result = DataUtil.executeNonQuery(cmd, arrParams);
                if (result)
                    Constant.USER_INFORMATIONDTO = userInformations;
                
            }
            catch (Exception ex)
            {
                result = false;
            }
            DataUtil.disconnect();
            return result;
        }
        /* Author : Jenny
         * Date : 30/03/2012
         * function checkUserNameExists
         * parameter : username
         * check the username is exists, if exists warning to the manager change the username when create user
         * return : Boolean
         */
        public Boolean checkUserNameExists(String username)
        {
            Boolean result = true;
            try
            {
                DataUtil.connect();
                String cmdText = "sp_checkUserNameExists";
                MySqlParameter[] arrParam = new MySqlParameter[1];
                arrParam[0] = new MySqlParameter("_UserName", username);
                MySqlDataReader reader = DataUtil.executeReader(cmdText, arrParam);
                if (reader.Read())
                {
                    result = false;
                }
                
            }
            catch (Exception ex)
            {
                result = false;
            }
            DataUtil.disconnect();
            return result;
        }
        /* Author : Jenny
        * Date : 30/03/2012
        * function checkUserNameExists
        * parameter : newPassword, action, content
        * change password
        * return : Boolean
        */
        public Boolean changePassword(String newPassword, String action, String content)
        {
            Boolean result = true;
            try
            {
                DataUtil.connect();
                String cmdText = "sp_changePassword";
                MySqlParameter[] arrParam = new MySqlParameter[5];
                arrParam[0] = new MySqlParameter("_IDLogin", Constant.USER_LOGIN.Id);
                arrParam[1] = new MySqlParameter("_NewPassword", newPassword);
                arrParam[2] = new MySqlParameter("_Actions", action);
                arrParam[3] = new MySqlParameter("_Contents", content);
                arrParam[4] = new MySqlParameter("_IDLog", Constant.LOG_ID);
                result = DataUtil.executeNonQuery(cmdText, arrParam);
            }
            catch (Exception ex)
            {
                result = false;
            }
            DataUtil.disconnect();
            return result;
        }
        /* Author : Jenny
          * Date : 3/4/2012
          * function checkEmail
          * check the email is valid
          * return Boolean
          */
        public Boolean checkEmail(String email)
        {
            Regex emailregex = new Regex("(?<user>[^@]+)@(?<host>.+)");
            Match m = emailregex.Match(email);
            if (m.Success)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /* Author : Jenny
         * Date : 3/4/2012
         * function checkPassword
         * check the password strong enough
         * return String
         */
        public String checkPassword(String password)
        {
            int score = 0;
            if (Regex.IsMatch(password, @"[0-9]+(\.[0-9][0-9]?)?", RegexOptions.ECMAScript))      //number only //"^\d+$" if you need to match more than one digit.
                score++;
            if (Regex.IsMatch(password, @"^(?=.*[a-z])(?=.*[A-Z]).+$", RegexOptions.ECMAScript)) //both, lower and upper case
                score++;
            if (Regex.IsMatch(password, @"[!,@,#,$,%,^,&,*,?,_,~,-,L,(,)]", RegexOptions.ECMAScript)) //^[A-Z]+$
                score++;
            return score + "";
        }
        /* Author : Jenny
         * Date : 6/4/2012
         * function checkPhoneNumber
         * check phone must be number
         * return Boolean
         */
        public Boolean checkPhoneNumber(String phone)
        {
            Regex phoneRegex = new Regex("[0-9]+");
            Match m = phoneRegex.Match(phone);
            if (m.Success)
                return true;
            else
                return false;
        }
        public int forgetPassword(String username, String email)
        {
            int result = -4;
            if (Constant.IP == null)
                Constant.IP = getIP();
            try
            {
                DataUtil.connect();
                MySqlParameter[] arrPam = new MySqlParameter[1];
                arrPam[0] = new MySqlParameter("_UserName", username);
                MySqlDataReader reader = DataUtil.executeReader("sp_checkUserNameExists", arrPam);

                if (reader.Read())
                {
                    if (reader["Username"].ToString().Equals(username))
                    {
                        if (reader["Status"].ToString().Equals("0"))
                        {
                            result = -3;
                        }
                        else if (reader["Email"].ToString().Equals(email))
                        {
                            Constant.USER_LOGIN = new UserLoginDTO();
                            Constant.USER_LOGIN.Id = reader["ID"].ToString();
                            result = 0;
                        }
                        else
                        {
                            result = -1;
                            Constant.COUNT_FORGET_PASSWORD_FAIL = Convert.ToInt32(reader["CountForgetFail"].ToString()) + 1;
                            updateCountForgetPasswordFail(reader["ID"].ToString(), Constant.DR_ACTIONS_VARIABLE["varUpdate"].ToString(), "Input Wrong email");
                        }
                    }
                }
                else
                {
                    result = -2;
                }
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }
            DataUtil.disconnect();
            return result;
        }
        /* Author : Jenny
        * Date : 10/4/2012
        * function updateCountLogin
        * parameter id : login id
        * parameter action : such as add, update, delete write to log
        * parameter content : content of this action
        * send a notice to manager about the forget password
        * return value : Boolean
        */
        public Boolean sendForgetPassword(String id, String action, String content)
        {
            Boolean result = true;
            try
            {
                DataUtil.connect();
                MySqlParameter[] arrpam = new MySqlParameter[4];
                arrpam[0] = new MySqlParameter("_ID", id);
                arrpam[1] = new MySqlParameter("_IP", Constant.IP);
                arrpam[2] = new MySqlParameter("_Actions", action);
                arrpam[3] = new MySqlParameter("_Contents", content);

                String cmdText = "sp_sendForgetPassword";
                result = DataUtil.executeNonQuery(cmdText, arrpam);
                
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                result = false;
            }
            DataUtil.disconnect();
            return result;
        }
        /* Author : Jenny
        * Date : 10/4/2012
        * function updateCountForgetPasswordFail
        * parameter id : login id
        * parameter action : such as add, update, delete write to log
        * parameter content : content of this action
        * update how many times user send forget password fail, if fail 3 times account will be locked
        * return value : Boolean
        */
        public Boolean updateCountForgetPasswordFail(String id, String action, String content)
        {
            try
            {
                DataUtil.connect();
                MySqlParameter[] arrpam = new MySqlParameter[5];
                arrpam[0] = new MySqlParameter("_ID", id);
                arrpam[1] = new MySqlParameter("_IP", Constant.IP);
                arrpam[2] = new MySqlParameter("_CountForgetFail", Constant.COUNT_FORGET_PASSWORD_FAIL);
                arrpam[3] = new MySqlParameter("_Actions", action);
                arrpam[4] = new MySqlParameter("_Contents", content);

                String cmdText = "sp_updateCountForgetPasswordFail";
                if (DataUtil.executeNonQuery(cmdText, arrpam))
                {
                    return true;
                }
                else
                    return false;
                DataUtil.disconnect();
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                return false;
            }
        }
        public Boolean logoutByUserLoginID()
        {
            Boolean result = true;
            try
            {
                DataUtil.connect();
                String cmdText = "sp_LogoutByUserLoginID";
                MySqlParameter[] arrParam = new MySqlParameter[1];
                arrParam[0] = new MySqlParameter("_ID", Constant.USER_LOGIN.Id);
                result = DataUtil.executeNonQuery(cmdText, arrParam);
                DataUtil.disconnect();
            }
            catch (Exception ex)
            {
                result = false;
            }
            return result;
        }
        public DataTable getAllUser()
        {
            DataTable dt = new DataTable();
            try
            {
                DataUtil.connect();
                String cmdText = "sp_getAllUser";
                dt = DataUtil.executeStore(cmdText, null);
            }
            catch (Exception ex)
            {

            }
            return dt;
        }
        public Boolean lockOrUnlockUserByLoginID(String loginID, String status, String firstName)
        {
            Boolean result = false;
            try
            {
                DataUtil.connect();
                String cmdText = "sp_lockOrUnlockUserByLoginID";
                MySqlParameter[] arrParam = new MySqlParameter[5];
                arrParam[0] = new MySqlParameter("_LoginID", loginID);
                arrParam[1] = new MySqlParameter("_Status", status);
                arrParam[2] = new MySqlParameter("_IDLog", Constant.LOG_ID);
                if(status.Equals("0"))
                    arrParam[3] = new MySqlParameter("_Actions", Constant.DR_ACTIONS_VARIABLE["varLockUser"]);
                else
                    arrParam[3] = new MySqlParameter("_Actions", Constant.DR_ACTIONS_VARIABLE["varUnlockUser"]);
                arrParam[4] = new MySqlParameter("_Contents", firstName);
                result = DataUtil.executeNonQuery(cmdText, arrParam);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return result;
        }
        public Boolean resetPasswordUserByLoginID(String loginID, String firstName)
        {
            Boolean result = false;
            String password = "";
            try
            {
                DataUtil.connect();
                String cmdText = "sp_resetPasswordUserByLoginID";
                MySqlParameter[] arrParam = new MySqlParameter[5];
                arrParam[0] = new MySqlParameter("_LoginID", loginID);
                arrParam[1] = new MySqlParameter("_Password", "111111");
                arrParam[2] = new MySqlParameter("_IDLog", Constant.LOG_ID);
                arrParam[3] = new MySqlParameter("_Actions", Constant.DR_ACTIONS_VARIABLE["varResetPassword"]);
                arrParam[4] = new MySqlParameter("_Contents", firstName);
                result = DataUtil.executeNonQuery(cmdText, arrParam);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return result;
        }
        public String sortDate(String date)
        {
            String[] arr = date.Split(new char[] {'/'});
            return arr[2] + "/" + arr[0] + "/" + arr[1];
        }
        public DataTable getUserByID(String idLogin)
        {
            DataTable dt = new DataTable();
            try
            {
                DataUtil.connect();
                String cmdText = "sp_GetUserByID";
                MySqlParameter[] arrParam = new MySqlParameter[1];
                arrParam[0] = new MySqlParameter("_IDLogin", idLogin);
                dt = DataUtil.executeStore(cmdText, arrParam);
            }
            catch (Exception ex)
            {
                String meg = ex.Message;
            }
            return dt;
        }
        public DataTable getAllUserPermission(String idLogin, String idPermissionType)
        {
            DataTable dt = new DataTable();
            try
            {
                DataUtil.connect();
                String cmdText = "sp_GetAllUserPermissionByIDLogin";
                MySqlParameter[] arrParam = new MySqlParameter[2];
                arrParam[0] = new MySqlParameter("_IDLogin", idLogin);
                arrParam[1] = new MySqlParameter("_IDPermissionType", idPermissionType);
                dt = DataUtil.executeStore(cmdText, arrParam);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return dt;
        }
        public Boolean updateUserPermission(String cmdText, String firstName)
        {
            Boolean result = false;
            try
            {
                DataUtil.connect();
                MySqlParameter[] arrParam = new MySqlParameter[4];
                arrParam[0] = new MySqlParameter("_cmdText", cmdText);
                arrParam[1] = new MySqlParameter("_IDLog", Constant.LOG_ID);
                arrParam[2] = new MySqlParameter("_Actions", Constant.DR_ACTIONS_VARIABLE["varUpdate"]);
                arrParam[3] = new MySqlParameter("_Contents", "Permissions of " + firstName);
                cmdText = "sp_UpdateUserPermission";
                result = DataUtil.executeNonQuery(cmdText, arrParam);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return result;
        }
        public Boolean updateUserPermissionbyColumn(String typeColumn, String idLogin, String idType, String status, String firstName)
        {
            Boolean result = false;
            try
            {
                DataUtil.connect();
                MySqlParameter[] arrParam = new MySqlParameter[7];
                arrParam[0] = new MySqlParameter("_TypeColumn", typeColumn);
                arrParam[1] = new MySqlParameter("_IDLogin", idLogin);
                arrParam[2] = new MySqlParameter("_IDType", idType);
                arrParam[3] = new MySqlParameter("_Status", status);
                arrParam[4] = new MySqlParameter("_IDLog", Constant.LOG_ID);
                arrParam[5] = new MySqlParameter("_Actions", Constant.DR_ACTIONS_VARIABLE["varUpdate"]);
                arrParam[6] = new MySqlParameter("_Contents", "Permissions of " + firstName);
                String cmdText = "sp_updateUserPermissionbyColumn";
                result = DataUtil.executeNonQuery(cmdText, arrParam);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return result;
        }
        public DataTable[] getUsersNoGroupAndUsersByGroupID(String idGroup)
        {
            DataTable[] dts = new DataTable[2];
            try
            {
                DataUtil.connect();
                String cmdText = "sp_getUsersNoGroupAndUsersByGroupID";
                MySqlParameter[] arrParam = new MySqlParameter[1];
                arrParam[0] = new MySqlParameter("_IDGroup", idGroup);
                MySqlDataAdapter adapter = DataUtil.executeStoreReturnAdapter(cmdText, arrParam);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                dts[0] = ds.Tables[0];
                dts[1] = ds.Tables[1];
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return dts;
        }
        public Boolean addUserIntoGroup(String idInformations, String idGroup)
        {
            Boolean result = false;
            try
            {
                DataUtil.connect();
                MySqlParameter[] arrParam = new MySqlParameter[5];
                arrParam[0] = new MySqlParameter("_IDInformations", idInformations);
                arrParam[1] = new MySqlParameter("_IDGroup", idGroup);
                arrParam[2] = new MySqlParameter("_IDLog", Constant.LOG_ID);
                arrParam[3] = new MySqlParameter("_Actions", Constant.DR_ACTIONS_VARIABLE["varUpdate"]);
                arrParam[4] = new MySqlParameter("_Contents", "Add user into Group");
                String cmdText = "sp_addUserIntoGroup";
                result = DataUtil.executeNonQuery(cmdText, arrParam);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return result;
        }
        public Boolean removeGroupOfUser(String idInformations, String firstName)
        {
            Boolean result = false;
            try
            {
                DataUtil.connect();
                MySqlParameter[] arrParam = new MySqlParameter[4];
                arrParam[0] = new MySqlParameter("_IDInformations", idInformations);
                arrParam[1] = new MySqlParameter("_IDLog", Constant.LOG_ID);
                arrParam[2] = new MySqlParameter("_Actions", Constant.DR_ACTIONS_VARIABLE["varUpdate"]);
                arrParam[3] = new MySqlParameter("_Contents", "Remove group of " + firstName);
                String cmdText = "sp_removeGroupOfUser";
                result = DataUtil.executeNonQuery(cmdText, arrParam);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return result;
        }
    }
}
